package com.wlcorp.forcazamis.sys.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.UnsupportedEncodingException;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class XLSFactory {

	private static XLSFactory instance;

	private HSSFWorkbook wb;

	private HSSFSheet sheet;

	private HSSFRow row;

	private HSSFCell cell;

	private XLSFactory() {

	}

	public static XLSFactory getInstance() {
		if (null == instance) {
			instance = new XLSFactory();
		}
		return instance;
	}

	public HSSFWorkbook getXLSTemplate1(String excelPath) {
		try {
			excelPath = new String(excelPath.getBytes("iso-8859-1"), "utf-8");
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
					excelPath));
			this.wb = new HSSFWorkbook(fs);
			this.sheet = wb.getSheetAt(0);
			wb.setSheetName(0, "sheet1");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return wb;
	}

	public HSSFWorkbook getXLSTemplate(String excelPath) {
		try {
			excelPath = new String(excelPath.getBytes("iso-8859-1"), "utf-8");
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
					excelPath));
			this.wb = new HSSFWorkbook(fs);
			// sheet = wb.getSheetAt(0);
			// wb.setSheetName(0, "sheet1");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return wb;
	}

	public void exportXLS(HttpServletResponse response) {
		try {
			wb.write(response.getOutputStream());
			response.getOutputStream().flush();
			response.getOutputStream().close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public void setXLS(HttpServletResponse response, String exportFileName) {
		// 导出设置
		response.reset();
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("content-disposITion", "attachment;filename="
				+ exportFileName);// 下载
	}

	/**
	 * 增加一行
	 * 
	 * @param index
	 *            行号
	 */
	public void createRow(int index) {
		this.row = this.sheet.createRow(index);
	}

	/**
	 * 取得一行
	 * 
	 * @param index
	 *            行号
	 */
	public void getRow(int index) {
		this.row = this.sheet.getRow(index);
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCellCreate(int index, String value) {
		HSSFCell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(new HSSFRichTextString(value));
		this.cell = cell;
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCellCreate(int index, int value) {
		HSSFCell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
		this.cell = cell;
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCellCreate(int index, double value) {
		HSSFCell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
		
		HSSFCellStyle cs = wb.createCellStyle();
		cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);//   下边框   
		cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);//   左边框   
		cs.setBorderRight(HSSFCellStyle.BORDER_THIN);//   右边框   
		cs.setBorderTop(HSSFCellStyle.BORDER_THIN);//   上边框 
		cell.setCellStyle(cs);
		
		this.cell = cell;
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCellExist(int index, String value) {
		HSSFCell cell = this.row.getCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(new HSSFRichTextString(value));
		this.cell = cell;
	}

	public void setCellExist(int index, Double value) {
		HSSFCell cell = this.row.getCell((short) index);
		cell.setCellValue(value);
		this.cell = cell;
	}

	public void setCellExist(int index, Integer value) {
		HSSFCell cell = this.row.getCell((short) index);
		cell.setCellValue(value);
		this.cell = cell;
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCell(int index, int value) {
		HSSFCell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
		
		HSSFCellStyle cs = wb.createCellStyle();
		cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);//   下边框   
		cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);//   左边框   
		cs.setBorderRight(HSSFCellStyle.BORDER_THIN);//   右边框   
		cs.setBorderTop(HSSFCellStyle.BORDER_THIN);//   上边框 
		cell.setCellStyle(cs);
		this.cell = cell;
	}

	public void setCellStyleBold(HSSFCell cell) {
		// 让HSSFWorkbook创建一个单元格样式的对象
		HSSFCellStyle cellStyle = wb.createCellStyle();
		// 将字体对象赋值给单元格样式对象
		HSSFFont font = this.wb.createFont();
		font.setFontName("Arial");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		cellStyle.setFont(font);
		cell.setCellStyle(cellStyle);
	}

	public void setWrapText(HSSFCell cell) {
		HSSFCellStyle cs = wb.createCellStyle();
		HSSFFont font = this.wb.createFont();
		font.setFontName("Arial");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		cs.setFont(font);
		cs.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		cs.setWrapText(true);
		cell.setCellStyle(cs);
	}

	public void setCellStyleBackground(HSSFCell cell) {
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		cellStyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER); 
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
		cell.setCellStyle(cellStyle);
	}

	public HSSFSheet getSheet() {
		return sheet;
	}

	public void setSheet(HSSFSheet sheet) {
		this.sheet = sheet;
	}

	public HSSFCell getCell() {
		return cell;
	}

	public void setCell(HSSFCell cell) {
		this.cell = cell;
	}

	public HSSFWorkbook getWb() {
		return wb;
	}

	public void setWb(HSSFWorkbook wb) {
		this.wb = wb;
	}

}
